{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Window LAG\n",
    "\n",
    "## COVID-19 Data\n",
    "Notes on the data: This data was assembled based on work done by [Rodrigo Pombo](https://github.com/pomber/covid19) based on [John Hopkins University](https://systems.jhu.edu/research/public-health/ncov/), based on [World Health Organisation](https://www.who.int/health-topics/coronavirus). The data was assembled 21st April 2020 - there are no plans to keep this data set up to date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prerequesites\n",
    "from pyhive import hive\n",
    "%load_ext sql\n",
    "%sql hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Window Function\n",
    "The SQL Window functions include LAG, LEAD, RANK and NTILE. These functions operate over a \"window\" of rows - typically these are rows in the table that are in some sense adjacent."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Introducing the `covid` table\n",
    "\n",
    "The example uses a WHERE clause to show the cases in 'Italy' in March.\n",
    "\n",
    "```sql\n",
    "SELECT name, DAY(whn),\n",
    " confirmed, deaths, recovered\n",
    " FROM covid\n",
    "WHERE name = 'Italy'\n",
    "AND MONTH(whn) = 3\n",
    "ORDER BY whn\n",
    "```\n",
    "\n",
    "**Modify the query to show data from Spain**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>day</th>\n",
       "        <th>confirmed</th>\n",
       "        <th>deaths</th>\n",
       "        <th>recovered</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>1</td>\n",
       "        <td>84</td>\n",
       "        <td>0</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>2</td>\n",
       "        <td>120</td>\n",
       "        <td>0</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>3</td>\n",
       "        <td>165</td>\n",
       "        <td>1</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>4</td>\n",
       "        <td>222</td>\n",
       "        <td>2</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>5</td>\n",
       "        <td>259</td>\n",
       "        <td>3</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>6</td>\n",
       "        <td>400</td>\n",
       "        <td>5</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>7</td>\n",
       "        <td>500</td>\n",
       "        <td>10</td>\n",
       "        <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>8</td>\n",
       "        <td>673</td>\n",
       "        <td>17</td>\n",
       "        <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>9</td>\n",
       "        <td>1073</td>\n",
       "        <td>28</td>\n",
       "        <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>10</td>\n",
       "        <td>1695</td>\n",
       "        <td>35</td>\n",
       "        <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>11</td>\n",
       "        <td>2277</td>\n",
       "        <td>54</td>\n",
       "        <td>183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>12</td>\n",
       "        <td>2277</td>\n",
       "        <td>55</td>\n",
       "        <td>183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>13</td>\n",
       "        <td>5232</td>\n",
       "        <td>133</td>\n",
       "        <td>193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>14</td>\n",
       "        <td>6391</td>\n",
       "        <td>195</td>\n",
       "        <td>517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>15</td>\n",
       "        <td>7798</td>\n",
       "        <td>289</td>\n",
       "        <td>517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>16</td>\n",
       "        <td>9942</td>\n",
       "        <td>342</td>\n",
       "        <td>530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>17</td>\n",
       "        <td>11748</td>\n",
       "        <td>533</td>\n",
       "        <td>1028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>18</td>\n",
       "        <td>13910</td>\n",
       "        <td>623</td>\n",
       "        <td>1081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>19</td>\n",
       "        <td>17963</td>\n",
       "        <td>830</td>\n",
       "        <td>1107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>20</td>\n",
       "        <td>20410</td>\n",
       "        <td>1043</td>\n",
       "        <td>1588</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">31 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('Spain', 1, 84, 0, 2),\n",
       " ('Spain', 2, 120, 0, 2),\n",
       " ('Spain', 3, 165, 1, 2),\n",
       " ('Spain', 4, 222, 2, 2),\n",
       " ('Spain', 5, 259, 3, 2),\n",
       " ('Spain', 6, 400, 5, 2),\n",
       " ('Spain', 7, 500, 10, 30),\n",
       " ('Spain', 8, 673, 17, 30),\n",
       " ('Spain', 9, 1073, 28, 32),\n",
       " ('Spain', 10, 1695, 35, 32),\n",
       " ('Spain', 11, 2277, 54, 183),\n",
       " ('Spain', 12, 2277, 55, 183),\n",
       " ('Spain', 13, 5232, 133, 193),\n",
       " ('Spain', 14, 6391, 195, 517),\n",
       " ('Spain', 15, 7798, 289, 517),\n",
       " ('Spain', 16, 9942, 342, 530),\n",
       " ('Spain', 17, 11748, 533, 1028),\n",
       " ('Spain', 18, 13910, 623, 1081),\n",
       " ('Spain', 19, 17963, 830, 1107),\n",
       " ('Spain', 20, 20410, 1043, 1588),\n",
       " ('Spain', 21, 25374, 1375, 2125),\n",
       " ('Spain', 22, 28768, 1772, 2575),\n",
       " ('Spain', 23, 35136, 2311, 2575),\n",
       " ('Spain', 24, 39885, 2808, 3794),\n",
       " ('Spain', 25, 49515, 3647, 5367),\n",
       " ('Spain', 26, 57786, 4365, 7015),\n",
       " ('Spain', 27, 65719, 5138, 9357),\n",
       " ('Spain', 28, 73235, 5982, 12285),\n",
       " ('Spain', 29, 80110, 6803, 14709),\n",
       " ('Spain', 30, 87956, 7716, 16780),\n",
       " ('Spain', 31, 95923, 8464, 19259)]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, DAY(whn) day, confirmed, deaths, recovered\n",
    "  FROM covid\n",
    "    WHERE name='Spain' AND MONTH(whn)=3\n",
    "    ORDER BY day"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Introducing the LAG function\n",
    "\n",
    "The LAG function is used to show data from the preceding row or the table. When lining up rows the data is partitioned by country name and ordered by the data whn. That means that only data from Italy is considered.\n",
    "\n",
    "```sql\n",
    "SELECT name, DAY(whn), confirmed,\n",
    "   LAG(whn, 1) OVER (PARTITION BY name ORDER BY whn)\n",
    " FROM covid\n",
    "WHERE name = 'Italy'\n",
    "AND MONTH(whn) = 3\n",
    "ORDER BY whn\n",
    "```\n",
    "\n",
    "**Modify the query to show confirmed for the day before.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>day</th>\n",
       "        <th>confirmed</th>\n",
       "        <th>lag_confirmed</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>1</td>\n",
       "        <td>1694</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2</td>\n",
       "        <td>2036</td>\n",
       "        <td>1694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>3</td>\n",
       "        <td>2502</td>\n",
       "        <td>2036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>4</td>\n",
       "        <td>3089</td>\n",
       "        <td>2502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>5</td>\n",
       "        <td>3858</td>\n",
       "        <td>3089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>6</td>\n",
       "        <td>4636</td>\n",
       "        <td>3858</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>7</td>\n",
       "        <td>5883</td>\n",
       "        <td>4636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>8</td>\n",
       "        <td>7375</td>\n",
       "        <td>5883</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>9</td>\n",
       "        <td>9172</td>\n",
       "        <td>7375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>10</td>\n",
       "        <td>10149</td>\n",
       "        <td>9172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>11</td>\n",
       "        <td>12462</td>\n",
       "        <td>10149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>12</td>\n",
       "        <td>12462</td>\n",
       "        <td>12462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>13</td>\n",
       "        <td>17660</td>\n",
       "        <td>12462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>14</td>\n",
       "        <td>21157</td>\n",
       "        <td>17660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>15</td>\n",
       "        <td>24747</td>\n",
       "        <td>21157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>16</td>\n",
       "        <td>27980</td>\n",
       "        <td>24747</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>17</td>\n",
       "        <td>31506</td>\n",
       "        <td>27980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>18</td>\n",
       "        <td>35713</td>\n",
       "        <td>31506</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>19</td>\n",
       "        <td>41035</td>\n",
       "        <td>35713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>20</td>\n",
       "        <td>47021</td>\n",
       "        <td>41035</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">31 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('Italy', 1, 1694, None),\n",
       " ('Italy', 2, 2036, 1694),\n",
       " ('Italy', 3, 2502, 2036),\n",
       " ('Italy', 4, 3089, 2502),\n",
       " ('Italy', 5, 3858, 3089),\n",
       " ('Italy', 6, 4636, 3858),\n",
       " ('Italy', 7, 5883, 4636),\n",
       " ('Italy', 8, 7375, 5883),\n",
       " ('Italy', 9, 9172, 7375),\n",
       " ('Italy', 10, 10149, 9172),\n",
       " ('Italy', 11, 12462, 10149),\n",
       " ('Italy', 12, 12462, 12462),\n",
       " ('Italy', 13, 17660, 12462),\n",
       " ('Italy', 14, 21157, 17660),\n",
       " ('Italy', 15, 24747, 21157),\n",
       " ('Italy', 16, 27980, 24747),\n",
       " ('Italy', 17, 31506, 27980),\n",
       " ('Italy', 18, 35713, 31506),\n",
       " ('Italy', 19, 41035, 35713),\n",
       " ('Italy', 20, 47021, 41035),\n",
       " ('Italy', 21, 53578, 47021),\n",
       " ('Italy', 22, 59138, 53578),\n",
       " ('Italy', 23, 63927, 59138),\n",
       " ('Italy', 24, 69176, 63927),\n",
       " ('Italy', 25, 74386, 69176),\n",
       " ('Italy', 26, 80589, 74386),\n",
       " ('Italy', 27, 86498, 80589),\n",
       " ('Italy', 28, 92472, 86498),\n",
       " ('Italy', 29, 97689, 92472),\n",
       " ('Italy', 30, 101739, 97689),\n",
       " ('Italy', 31, 105792, 101739)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, DAY(whn) day, confirmed,\n",
    "  LAG(confirmed, 1) OVER (PARTITION BY name ORDER BY whn) AS lag_confirmed\n",
    "    FROM covid\n",
    "    WHERE name='Italy' AND MONTH(whn)=3\n",
    "    ORDER BY day"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### LAG operation\n",
    "\n",
    "Here is the correct query showing the cases for the day before:\n",
    "\n",
    "```sql\n",
    "SELECT name, DAY(whn), confirmed,\n",
    "   LAG(confirmed, 1) OVER (partition by name ORDER BY whn) AS lag\n",
    " FROM covid\n",
    "WHERE name = 'Italy'\n",
    "AND MONTH(whn) = 3\n",
    "ORDER BY whn\n",
    "```\n",
    "\n",
    "Notice how the values in the LAG column match the value of the row diagonally above and to the left.\n",
    "\n",
    "name | DAY(whn) | confirmed | dbf\n",
    "------|---|------|----------\n",
    "Italy | 1 | **1694** | null\n",
    "Italy | 2 | 2036 | **1694**\n",
    "Italy | 3 | 2502 | 2036\n",
    "Italy | 4 | 3089 | 2502\n",
    "Italy | 5 | **3858** | 3089\n",
    "Italy | 6 | 4636 | **3858**\n",
    "Italy | 7 | 5883 | 4636\n",
    "Italy | 8 | 7375 | 5883\n",
    "Italy | 9 | 9172 | 7375\n",
    "Italy | 10 | 10149 | 9172\n",
    "... | | |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Number of new cases\n",
    "\n",
    "The number of confirmed case is cumulative - but we can use LAG to recover the number of new cases reported for each day.\n",
    "\n",
    "```sql\n",
    "SELECT name, DAY(whn), confirmed,\n",
    "   LAG(confirmed, 1) OVER (PARTITION BY name ORDER BY whn)\n",
    " FROM covid\n",
    "WHERE name = 'Italy'\n",
    "AND MONTH(whn) = 3\n",
    "ORDER BY whn\n",
    "```\n",
    "\n",
    "**Show the number of new cases for each day, for Italy, for March.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>day</th>\n",
       "        <th>new</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>1</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2</td>\n",
       "        <td>342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>3</td>\n",
       "        <td>466</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>4</td>\n",
       "        <td>587</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>5</td>\n",
       "        <td>769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>6</td>\n",
       "        <td>778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>7</td>\n",
       "        <td>1247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>8</td>\n",
       "        <td>1492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>9</td>\n",
       "        <td>1797</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>10</td>\n",
       "        <td>977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>11</td>\n",
       "        <td>2313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>12</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>13</td>\n",
       "        <td>5198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>14</td>\n",
       "        <td>3497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>15</td>\n",
       "        <td>3590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>16</td>\n",
       "        <td>3233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>17</td>\n",
       "        <td>3526</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>18</td>\n",
       "        <td>4207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>19</td>\n",
       "        <td>5322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>20</td>\n",
       "        <td>5986</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">31 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('Italy', 1, None),\n",
       " ('Italy', 2, 342),\n",
       " ('Italy', 3, 466),\n",
       " ('Italy', 4, 587),\n",
       " ('Italy', 5, 769),\n",
       " ('Italy', 6, 778),\n",
       " ('Italy', 7, 1247),\n",
       " ('Italy', 8, 1492),\n",
       " ('Italy', 9, 1797),\n",
       " ('Italy', 10, 977),\n",
       " ('Italy', 11, 2313),\n",
       " ('Italy', 12, 0),\n",
       " ('Italy', 13, 5198),\n",
       " ('Italy', 14, 3497),\n",
       " ('Italy', 15, 3590),\n",
       " ('Italy', 16, 3233),\n",
       " ('Italy', 17, 3526),\n",
       " ('Italy', 18, 4207),\n",
       " ('Italy', 19, 5322),\n",
       " ('Italy', 20, 5986),\n",
       " ('Italy', 21, 6557),\n",
       " ('Italy', 22, 5560),\n",
       " ('Italy', 23, 4789),\n",
       " ('Italy', 24, 5249),\n",
       " ('Italy', 25, 5210),\n",
       " ('Italy', 26, 6203),\n",
       " ('Italy', 27, 5909),\n",
       " ('Italy', 28, 5974),\n",
       " ('Italy', 29, 5217),\n",
       " ('Italy', 30, 4050),\n",
       " ('Italy', 31, 4053)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, DAY(whn) day, \n",
    "  confirmed - LAG(confirmed, 1) OVER (PARTITION BY name ORDER BY whn) AS new\n",
    "    FROM covid\n",
    "    WHERE name = 'Italy' AND MONTH(whn) = 3\n",
    "    ORDER BY day"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Weekly changes\n",
    "\n",
    "The data gathered are necessarily estimates and are inaccurate. However by taking a longer time span we can mitigate some of the effects.\n",
    "\n",
    "```sql\n",
    "SELECT name, DATE_FORMAT(whn,'%Y-%m-%d'), confirmed\n",
    " FROM covid\n",
    "WHERE name = 'Italy'\n",
    "AND WEEKDAY(whn) = 0\n",
    "ORDER BY whn\n",
    "```\n",
    "\n",
    "You can filter the data to view only Monday's figures **WHERE WEEKDAY(whn) = 0**.\n",
    "\n",
    "**Show the number of new cases in Italy for each week - show Monday only.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>whn</th>\n",
       "        <th>new</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-01-27</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-02-03</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-02-10</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-02-17</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-02-24</td>\n",
       "        <td>226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-03-02</td>\n",
       "        <td>1807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-03-09</td>\n",
       "        <td>7136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-03-16</td>\n",
       "        <td>18808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-03-23</td>\n",
       "        <td>35947</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-03-30</td>\n",
       "        <td>37812</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-04-06</td>\n",
       "        <td>30808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-04-13</td>\n",
       "        <td>26969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-04-20</td>\n",
       "        <td>21712</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Italy', '2020-01-27', None),\n",
       " ('Italy', '2020-02-03', 2),\n",
       " ('Italy', '2020-02-10', 1),\n",
       " ('Italy', '2020-02-17', 0),\n",
       " ('Italy', '2020-02-24', 226),\n",
       " ('Italy', '2020-03-02', 1807),\n",
       " ('Italy', '2020-03-09', 7136),\n",
       " ('Italy', '2020-03-16', 18808),\n",
       " ('Italy', '2020-03-23', 35947),\n",
       " ('Italy', '2020-03-30', 37812),\n",
       " ('Italy', '2020-04-06', 30808),\n",
       " ('Italy', '2020-04-13', 26969),\n",
       " ('Italy', '2020-04-20', 21712)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, DATE_FORMAT(whn, 'yyyy-MM-dd') whn, \n",
    "  confirmed-LAG(confirmed, 1) OVER (PARTITION BY name ORDER BY whn) AS new\n",
    "    FROM covid\n",
    "    WHERE name = 'Italy' AND DATE_FORMAT(whn, 'u') = '1'\n",
    "    ORDER BY whn"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. LAG using a JOIN\n",
    "\n",
    "You can JOIN a table using DATE arithmetic. This will give different results if data is missing.\n",
    "\n",
    "```sql\n",
    "SELECT tw.name, DATE_FORMAT(tw.whn,'%Y-%m-%d'), \n",
    " tw.confirmed, lw.confirmed\n",
    " FROM covid tw LEFT JOIN covid lw ON \n",
    "  DATE_ADD(lw.whn, INTERVAL 1 WEEK) = tw.whn\n",
    "   AND tw.name=lw.name\n",
    "WHERE tw.name = 'Italy'\n",
    "ORDER BY tw.whn\n",
    "```\n",
    "\n",
    "**Show the number of new cases in Italy for each week - show Monday only.**\n",
    "\n",
    "In the sample query we JOIN this week tw with last week lw using the DATE_ADD function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>whn</th>\n",
       "        <th>new</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-01-27</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-02-03</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-02-10</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-02-17</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-02-24</td>\n",
       "        <td>226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-03-02</td>\n",
       "        <td>1807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-03-09</td>\n",
       "        <td>7136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-03-16</td>\n",
       "        <td>18808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-03-23</td>\n",
       "        <td>35947</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-03-30</td>\n",
       "        <td>37812</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-04-06</td>\n",
       "        <td>30808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-04-13</td>\n",
       "        <td>26969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-04-20</td>\n",
       "        <td>21712</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Italy', '2020-01-27', None),\n",
       " ('Italy', '2020-02-03', 2),\n",
       " ('Italy', '2020-02-10', 1),\n",
       " ('Italy', '2020-02-17', 0),\n",
       " ('Italy', '2020-02-24', 226),\n",
       " ('Italy', '2020-03-02', 1807),\n",
       " ('Italy', '2020-03-09', 7136),\n",
       " ('Italy', '2020-03-16', 18808),\n",
       " ('Italy', '2020-03-23', 35947),\n",
       " ('Italy', '2020-03-30', 37812),\n",
       " ('Italy', '2020-04-06', 30808),\n",
       " ('Italy', '2020-04-13', 26969),\n",
       " ('Italy', '2020-04-20', 21712)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT tw.name, DATE_FORMAT(tw.whn,'yyyy-MM-dd') whn, \n",
    "  tw.confirmed - lw.confirmed AS new\n",
    "    FROM (SELECT * FROM covid WHERE name = 'Italy' AND DATE_FORMAT(whn, 'u')='1') tw\n",
    "    LEFT JOIN covid lw ON \n",
    "    (DATE_ADD(lw.whn, 7) = tw.whn AND tw.name=lw.name)\n",
    "    ORDER BY whn"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. RANK()\n",
    "\n",
    "The query shown shows the number of confirmed cases together with the world ranking for cases.\n",
    "\n",
    "United States has the highest number, Spain is number 2...\n",
    "\n",
    "Notice that while Spain has the second highest confirmed cases, Italy has the second highest number of deaths due to the virus.\n",
    "\n",
    "```sql\n",
    "SELECT \n",
    "   name,\n",
    "   confirmed,\n",
    "   RANK() OVER (ORDER BY confirmed DESC) rc,\n",
    "   deaths\n",
    "  FROM covid\n",
    "WHERE whn = '2020-04-20'\n",
    "ORDER BY confirmed DESC\n",
    "```\n",
    "\n",
    "**Include the ranking for the number of deaths in the table. Only include countries with a population of at least 10 million.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>confirmed</th>\n",
       "        <th>rc</th>\n",
       "        <th>deaths</th>\n",
       "        <th>rc2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "        <td>784326</td>\n",
       "        <td>1</td>\n",
       "        <td>42094</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>200210</td>\n",
       "        <td>2</td>\n",
       "        <td>20852</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>181228</td>\n",
       "        <td>3</td>\n",
       "        <td>24114</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>France</td>\n",
       "        <td>156480</td>\n",
       "        <td>4</td>\n",
       "        <td>20292</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Germany</td>\n",
       "        <td>147065</td>\n",
       "        <td>5</td>\n",
       "        <td>4862</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United Kingdom</td>\n",
       "        <td>125856</td>\n",
       "        <td>6</td>\n",
       "        <td>16550</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Turkey</td>\n",
       "        <td>90980</td>\n",
       "        <td>7</td>\n",
       "        <td>2140</td>\n",
       "        <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>83817</td>\n",
       "        <td>8</td>\n",
       "        <td>4636</td>\n",
       "        <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Iran</td>\n",
       "        <td>83505</td>\n",
       "        <td>9</td>\n",
       "        <td>5209</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>47121</td>\n",
       "        <td>10</td>\n",
       "        <td>405</td>\n",
       "        <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>40743</td>\n",
       "        <td>11</td>\n",
       "        <td>2587</td>\n",
       "        <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Belgium</td>\n",
       "        <td>39983</td>\n",
       "        <td>12</td>\n",
       "        <td>5828</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "        <td>37657</td>\n",
       "        <td>13</td>\n",
       "        <td>1725</td>\n",
       "        <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Netherlands</td>\n",
       "        <td>33588</td>\n",
       "        <td>14</td>\n",
       "        <td>3764</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Portugal</td>\n",
       "        <td>20863</td>\n",
       "        <td>15</td>\n",
       "        <td>735</td>\n",
       "        <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>India</td>\n",
       "        <td>18539</td>\n",
       "        <td>16</td>\n",
       "        <td>592</td>\n",
       "        <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Peru</td>\n",
       "        <td>16325</td>\n",
       "        <td>17</td>\n",
       "        <td>445</td>\n",
       "        <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sweden</td>\n",
       "        <td>14777</td>\n",
       "        <td>18</td>\n",
       "        <td>1580</td>\n",
       "        <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Japan</td>\n",
       "        <td>10797</td>\n",
       "        <td>19</td>\n",
       "        <td>236</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>South Korea</td>\n",
       "        <td>10674</td>\n",
       "        <td>20</td>\n",
       "        <td>236</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">90 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('United States', 784326, 1, 42094, 1),\n",
       " ('Spain', 200210, 2, 20852, 3),\n",
       " ('Italy', 181228, 3, 24114, 2),\n",
       " ('France', 156480, 4, 20292, 4),\n",
       " ('Germany', 147065, 5, 4862, 8),\n",
       " ('United Kingdom', 125856, 6, 16550, 5),\n",
       " ('Turkey', 90980, 7, 2140, 12),\n",
       " ('China', 83817, 8, 4636, 9),\n",
       " ('Iran', 83505, 9, 5209, 7),\n",
       " ('Russia', 47121, 10, 405, 23),\n",
       " ('Brazil', 40743, 11, 2587, 11),\n",
       " ('Belgium', 39983, 12, 5828, 6),\n",
       " ('Canada', 37657, 13, 1725, 13),\n",
       " ('Netherlands', 33588, 14, 3764, 10),\n",
       " ('Portugal', 20863, 15, 735, 15),\n",
       " ('India', 18539, 16, 592, 17),\n",
       " ('Peru', 16325, 17, 445, 21),\n",
       " ('Sweden', 14777, 18, 1580, 14),\n",
       " ('Japan', 10797, 19, 236, 27),\n",
       " ('South Korea', 10674, 20, 236, 27),\n",
       " ('Chile', 10507, 21, 139, 35),\n",
       " ('Saudi Arabia', 10484, 22, 103, 38),\n",
       " ('Ecuador', 10128, 23, 507, 19),\n",
       " ('Poland', 9593, 24, 380, 25),\n",
       " ('Romania', 8936, 25, 478, 20),\n",
       " ('Pakistan', 8418, 26, 176, 32),\n",
       " ('Mexico', 8261, 27, 686, 16),\n",
       " ('Czech Republic', 6900, 28, 194, 30),\n",
       " ('Indonesia', 6760, 29, 590, 18),\n",
       " ('Australia', 6547, 30, 67, 42),\n",
       " ('Philippines', 6459, 31, 428, 22),\n",
       " ('Ukraine', 5710, 32, 151, 33),\n",
       " ('Malaysia', 5425, 33, 89, 40),\n",
       " ('Dominican Republic', 4964, 34, 235, 29),\n",
       " ('Colombia', 3977, 35, 189, 31),\n",
       " ('Egypt', 3333, 36, 250, 26),\n",
       " ('South Africa', 3300, 37, 58, 43),\n",
       " ('Morocco', 3046, 38, 143, 34),\n",
       " ('Bangladesh', 2948, 39, 101, 39),\n",
       " ('Argentina', 2941, 40, 136, 36),\n",
       " ('Thailand', 2792, 41, 47, 44),\n",
       " ('Algeria', 2718, 42, 384, 24),\n",
       " ('Greece', 2245, 43, 116, 37),\n",
       " ('Kazakhstan', 1852, 44, 19, 54),\n",
       " ('Uzbekistan', 1627, 45, 5, 68),\n",
       " ('Iraq', 1574, 46, 82, 41),\n",
       " ('Azerbaijan', 1436, 47, 19, 54),\n",
       " ('Cameroon', 1163, 48, 42, 45),\n",
       " ('Cuba', 1087, 49, 36, 48),\n",
       " ('Ghana', 1042, 50, 9, 60),\n",
       " ('Afghanistan', 1026, 51, 36, 48),\n",
       " ('Tunisia', 884, 52, 38, 46),\n",
       " (\"Côte d'Ivoire\", 847, 53, 9, 60),\n",
       " ('Nigeria', 665, 54, 22, 52),\n",
       " ('Niger', 648, 55, 20, 53),\n",
       " ('Guinea', 622, 56, 5, 68),\n",
       " ('Burkina Faso', 581, 57, 38, 46),\n",
       " ('Bolivia', 564, 58, 33, 50),\n",
       " ('Jordan', 425, 59, 7, 64),\n",
       " ('Taiwan', 422, 60, 6, 67),\n",
       " ('Senegal', 377, 61, 5, 68),\n",
       " ('Congo, Democratic Republic of', 332, 62, 25, 51),\n",
       " ('Sri Lanka', 304, 63, 7, 64),\n",
       " ('Guatemala', 289, 64, 7, 64),\n",
       " ('Kenya', 281, 65, 14, 56),\n",
       " ('Vietnam', 268, 66, 0, 81),\n",
       " ('Venezuela', 256, 67, 9, 60),\n",
       " ('Tanzania', 254, 68, 10, 59),\n",
       " ('Mali', 246, 69, 14, 56),\n",
       " ('Somalia', 237, 70, 8, 63),\n",
       " ('Rwanda', 147, 71, 0, 81),\n",
       " ('Cambodia', 122, 72, 0, 81),\n",
       " ('Madagascar', 121, 73, 0, 81),\n",
       " ('Myanmar', 119, 74, 5, 68),\n",
       " ('Ethiopia', 111, 75, 3, 72),\n",
       " ('Sudan', 107, 76, 12, 58),\n",
       " ('Zambia', 65, 77, 3, 72),\n",
       " ('Haiti', 57, 78, 3, 72),\n",
       " ('Uganda', 56, 79, 0, 81),\n",
       " ('Benin', 54, 80, 1, 79),\n",
       " ('Mozambique', 39, 81, 0, 81),\n",
       " ('Syria', 39, 81, 3, 72),\n",
       " ('Chad', 33, 83, 0, 81),\n",
       " ('Nepal', 31, 84, 0, 81),\n",
       " ('Zimbabwe', 25, 85, 3, 72),\n",
       " ('Angola', 24, 86, 2, 77),\n",
       " ('Malawi', 17, 87, 2, 77),\n",
       " ('Burundi', 5, 88, 1, 79),\n",
       " ('South Sudan', 4, 89, 0, 81),\n",
       " ('Yemen', 1, 90, 0, 81)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \n",
    "  covid.name, confirmed,\n",
    "    RANK() OVER (ORDER BY confirmed DESC) rc,\n",
    "    deaths, RANK() OVER (ORDER BY deaths DESC) rc2\n",
    "    FROM (SELECT * FROM covid WHERE whn = '2020-04-20') covid JOIN \n",
    "        (SELECT * FROM world WHERE population>=10000000) world ON\n",
    "        (covid.name=world.name)\n",
    "    ORDER BY confirmed DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Infection rate\n",
    "\n",
    "The query shown includes a JOIN t the world table so we can access the total population of each country and calculate infection rates (in cases per 100,000).\n",
    "\n",
    "```sql\n",
    "SELECT \n",
    "   world.name,\n",
    "   ROUND(100000*confirmed/population,0)\n",
    "  FROM covid JOIN world ON covid.name=world.name\n",
    "WHERE whn = '2020-04-20' AND population > 10000000\n",
    "ORDER BY population DESC\n",
    "```\n",
    "\n",
    "**Show the infect rate ranking for each country. Only include countries with a population of at least 10 million.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>pc_cfrm</th>\n",
       "        <th>rc</th>\n",
       "        <th>population</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>6.0</td>\n",
       "        <td>52</td>\n",
       "        <td>1402378640.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>India</td>\n",
       "        <td>1.0</td>\n",
       "        <td>28</td>\n",
       "        <td>1361503224.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "        <td>238.0</td>\n",
       "        <td>87</td>\n",
       "        <td>329583916.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>3.0</td>\n",
       "        <td>35</td>\n",
       "        <td>266911900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Pakistan</td>\n",
       "        <td>4.0</td>\n",
       "        <td>42</td>\n",
       "        <td>219412520.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>19.0</td>\n",
       "        <td>65</td>\n",
       "        <td>211442625.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nigeria</td>\n",
       "        <td>0.0</td>\n",
       "        <td>16</td>\n",
       "        <td>206139587.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bangladesh</td>\n",
       "        <td>2.0</td>\n",
       "        <td>32</td>\n",
       "        <td>168506944.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>32.0</td>\n",
       "        <td>71</td>\n",
       "        <td>146745098.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mexico</td>\n",
       "        <td>7.0</td>\n",
       "        <td>54</td>\n",
       "        <td>126577691.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Japan</td>\n",
       "        <td>9.0</td>\n",
       "        <td>59</td>\n",
       "        <td>125950000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Philippines</td>\n",
       "        <td>6.0</td>\n",
       "        <td>51</td>\n",
       "        <td>108545308.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Egypt</td>\n",
       "        <td>3.0</td>\n",
       "        <td>39</td>\n",
       "        <td>100300578.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ethiopia</td>\n",
       "        <td>0.0</td>\n",
       "        <td>7</td>\n",
       "        <td>98665000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Vietnam</td>\n",
       "        <td>0.0</td>\n",
       "        <td>15</td>\n",
       "        <td>96208984.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Iran</td>\n",
       "        <td>100.0</td>\n",
       "        <td>79</td>\n",
       "        <td>83396540.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Turkey</td>\n",
       "        <td>109.0</td>\n",
       "        <td>80</td>\n",
       "        <td>83154997.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Germany</td>\n",
       "        <td>177.0</td>\n",
       "        <td>82</td>\n",
       "        <td>83149300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Congo, Democratic Republic of</td>\n",
       "        <td>0.0</td>\n",
       "        <td>21</td>\n",
       "        <td>69360000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>France</td>\n",
       "        <td>233.0</td>\n",
       "        <td>86</td>\n",
       "        <td>67076000.0</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">90 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('China', 6.0, 52, 1402378640.0),\n",
       " ('India', 1.0, 28, 1361503224.0),\n",
       " ('United States', 238.0, 87, 329583916.0),\n",
       " ('Indonesia', 3.0, 35, 266911900.0),\n",
       " ('Pakistan', 4.0, 42, 219412520.0),\n",
       " ('Brazil', 19.0, 65, 211442625.0),\n",
       " ('Nigeria', 0.0, 16, 206139587.0),\n",
       " ('Bangladesh', 2.0, 32, 168506944.0),\n",
       " ('Russia', 32.0, 71, 146745098.0),\n",
       " ('Mexico', 7.0, 54, 126577691.0),\n",
       " ('Japan', 9.0, 59, 125950000.0),\n",
       " ('Philippines', 6.0, 51, 108545308.0),\n",
       " ('Egypt', 3.0, 39, 100300578.0),\n",
       " ('Ethiopia', 0.0, 7, 98665000.0),\n",
       " ('Vietnam', 0.0, 15, 96208984.0),\n",
       " ('Iran', 100.0, 79, 83396540.0),\n",
       " ('Turkey', 109.0, 80, 83154997.0),\n",
       " ('Germany', 177.0, 82, 83149300.0),\n",
       " ('Congo, Democratic Republic of', 0.0, 21, 69360000.0),\n",
       " ('France', 233.0, 86, 67076000.0),\n",
       " ('Thailand', 4.0, 45, 66497905.0),\n",
       " ('United Kingdom', 189.0, 83, 66435550.0),\n",
       " ('Italy', 301.0, 88, 60238522.0),\n",
       " ('South Africa', 6.0, 50, 58775022.0),\n",
       " ('Tanzania', 0.0, 18, 55890747.0),\n",
       " ('Myanmar', 0.0, 12, 54339766.0),\n",
       " ('South Korea', 21.0, 66, 51780579.0),\n",
       " ('Colombia', 8.0, 57, 49395678.0),\n",
       " ('Kenya', 1.0, 23, 47564296.0),\n",
       " ('Spain', 425.0, 90, 47100396.0),\n",
       " ('Argentina', 7.0, 55, 44938712.0),\n",
       " ('Algeria', 6.0, 53, 43000000.0),\n",
       " ('Sudan', 0.0, 14, 42456380.0),\n",
       " ('Ukraine', 14.0, 62, 41858119.0),\n",
       " ('Uganda', 0.0, 9, 40299300.0),\n",
       " ('Iraq', 4.0, 44, 39127900.0),\n",
       " ('Poland', 25.0, 68, 38379000.0),\n",
       " ('Canada', 99.0, 78, 38007166.0),\n",
       " ('Morocco', 8.0, 58, 35879846.0),\n",
       " ('Saudi Arabia', 31.0, 70, 34218169.0),\n",
       " ('Uzbekistan', 5.0, 47, 34148357.0),\n",
       " ('Malaysia', 17.0, 64, 32761760.0),\n",
       " ('Afghanistan', 3.0, 38, 32225560.0),\n",
       " ('Venezuela', 1.0, 24, 32219521.0),\n",
       " ('Peru', 51.0, 74, 32131400.0),\n",
       " ('Angola', 0.0, 4, 31127674.0),\n",
       " ('Ghana', 3.0, 40, 30280811.0),\n",
       " ('Mozambique', 0.0, 8, 30066648.0),\n",
       " ('Nepal', 0.0, 6, 29996478.0),\n",
       " ('Yemen', 0.0, 1, 29825968.0),\n",
       " ('Cameroon', 4.0, 46, 26545864.0),\n",
       " ('Madagascar', 0.0, 20, 26251309.0),\n",
       " ('Australia', 25.0, 69, 25690023.0),\n",
       " (\"Côte d'Ivoire\", 4.0, 41, 23919000.0),\n",
       " ('Taiwan', 2.0, 33, 23604265.0),\n",
       " ('Niger', 3.0, 37, 22314743.0),\n",
       " ('Sri Lanka', 1.0, 29, 21803000.0),\n",
       " ('Burkina Faso', 3.0, 36, 20870060.0),\n",
       " ('Mali', 1.0, 27, 20250833.0),\n",
       " ('Romania', 46.0, 72, 19405156.0),\n",
       " ('Malawi', 0.0, 5, 19129952.0),\n",
       " ('Chile', 55.0, 75, 19107216.0),\n",
       " ('Kazakhstan', 10.0, 61, 18689256.0),\n",
       " ('Zambia', 0.0, 17, 17885422.0),\n",
       " ('Syria', 0.0, 13, 17500657.0),\n",
       " ('Ecuador', 58.0, 76, 17472948.0),\n",
       " ('Netherlands', 192.0, 84, 17459431.0),\n",
       " ('Guatemala', 2.0, 31, 16604026.0),\n",
       " ('Chad', 0.0, 11, 16244513.0),\n",
       " ('Senegal', 2.0, 34, 16209125.0),\n",
       " ('Somalia', 1.0, 30, 15893219.0),\n",
       " ('Cambodia', 1.0, 25, 15288489.0),\n",
       " ('Zimbabwe', 0.0, 10, 15159624.0),\n",
       " ('South Sudan', 0.0, 2, 12778250.0),\n",
       " ('Rwanda', 1.0, 26, 12374397.0),\n",
       " ('Guinea', 5.0, 49, 12218357.0),\n",
       " ('Benin', 0.0, 19, 11733059.0),\n",
       " ('Tunisia', 8.0, 56, 11722038.0),\n",
       " ('Haiti', 0.0, 22, 11577779.0),\n",
       " ('Belgium', 347.0, 89, 11524454.0),\n",
       " ('Bolivia', 5.0, 48, 11469896.0),\n",
       " ('Cuba', 10.0, 60, 11209628.0),\n",
       " ('Burundi', 0.0, 3, 10953317.0),\n",
       " ('Greece', 21.0, 67, 10724599.0),\n",
       " ('Czech Republic', 65.0, 77, 10693939.0),\n",
       " ('Jordan', 4.0, 43, 10666772.0),\n",
       " ('Dominican Republic', 48.0, 73, 10358320.0),\n",
       " ('Sweden', 143.0, 81, 10338368.0),\n",
       " ('Portugal', 203.0, 85, 10276617.0),\n",
       " ('Azerbaijan', 14.0, 63, 10067108.0)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \n",
    "   world.name,\n",
    "    ROUND(100000* CAST(confirmed AS DOUBLE)/population) pc_cfrm,\n",
    "    RANK() OVER (ORDER BY confirmed/population) rc,\n",
    "    population\n",
    "    FROM covid JOIN world ON (covid.name=world.name)\n",
    "        WHERE whn = '2020-04-20' AND population > 10000000\n",
    "        ORDER BY population DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Turning the corner\n",
    "\n",
    "For each country that has had at last 1000 new cases in a single day, show the date of the peak number of new cases."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>whn</th>\n",
       "        <th>new_cases</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>2020-02-13</td>\n",
       "        <td>15136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-03-21</td>\n",
       "        <td>6557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Switzerland</td>\n",
       "        <td>2020-03-23</td>\n",
       "        <td>1321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Israel</td>\n",
       "        <td>2020-03-25</td>\n",
       "        <td>1131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>2020-03-25</td>\n",
       "        <td>9630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Austria</td>\n",
       "        <td>2020-03-26</td>\n",
       "        <td>1321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Germany</td>\n",
       "        <td>2020-03-27</td>\n",
       "        <td>6933</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Iran</td>\n",
       "        <td>2020-03-30</td>\n",
       "        <td>3186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "        <td>2020-04-05</td>\n",
       "        <td>2778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ecuador</td>\n",
       "        <td>2020-04-10</td>\n",
       "        <td>2196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ireland</td>\n",
       "        <td>2020-04-10</td>\n",
       "        <td>1515</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Netherlands</td>\n",
       "        <td>2020-04-10</td>\n",
       "        <td>1346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Portugal</td>\n",
       "        <td>2020-04-10</td>\n",
       "        <td>1516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United Kingdom</td>\n",
       "        <td>2020-04-10</td>\n",
       "        <td>8733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "        <td>2020-04-10</td>\n",
       "        <td>33755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Turkey</td>\n",
       "        <td>2020-04-11</td>\n",
       "        <td>5138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>France</td>\n",
       "        <td>2020-04-12</td>\n",
       "        <td>26849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Peru</td>\n",
       "        <td>2020-04-13</td>\n",
       "        <td>2265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Belgium</td>\n",
       "        <td>2020-04-15</td>\n",
       "        <td>2454</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>2020-04-17</td>\n",
       "        <td>3257</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">26 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('China', '2020-02-13', 15136),\n",
       " ('Italy', '2020-03-21', 6557),\n",
       " ('Switzerland', '2020-03-23', 1321),\n",
       " ('Israel', '2020-03-25', 1131),\n",
       " ('Spain', '2020-03-25', 9630),\n",
       " ('Austria', '2020-03-26', 1321),\n",
       " ('Germany', '2020-03-27', 6933),\n",
       " ('Iran', '2020-03-30', 3186),\n",
       " ('Canada', '2020-04-05', 2778),\n",
       " ('Ecuador', '2020-04-10', 2196),\n",
       " ('Ireland', '2020-04-10', 1515),\n",
       " ('Netherlands', '2020-04-10', 1346),\n",
       " ('Portugal', '2020-04-10', 1516),\n",
       " ('United Kingdom', '2020-04-10', 8733),\n",
       " ('United States', '2020-04-10', 33755),\n",
       " ('Turkey', '2020-04-11', 5138),\n",
       " ('France', '2020-04-12', 26849),\n",
       " ('Peru', '2020-04-13', 2265),\n",
       " ('Belgium', '2020-04-15', 2454),\n",
       " ('Brazil', '2020-04-17', 3257),\n",
       " ('Japan', '2020-04-17', 1161),\n",
       " ('Saudi Arabia', '2020-04-18', 1132),\n",
       " ('India', '2020-04-19', 1893),\n",
       " ('Russia', '2020-04-19', 6060),\n",
       " ('Belarus', '2020-04-20', 1485),\n",
       " ('Singapore', '2020-04-20', 1426)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "    SELECT name, whn,\n",
    "       COALESCE(confirmed-LAG(confirmed, 1) OVER \n",
    "                (PARTITION BY name ORDER BY whn), 0) AS new_cases\n",
    "    FROM covid\n",
    "), r AS (\n",
    "    SELECT name, whn, new_cases,\n",
    "       RANK() OVER (PARTITION BY name ORDER BY new_cases DESC) AS rank_new\n",
    "    FROM t\n",
    ")\n",
    "SELECT name, whn, new_cases FROM r\n",
    "    WHERE rank_new=1 AND new_cases>1000\n",
    "    ORDER BY whn, name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
